package com.dao;

import com.system.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class MaterialDaoImpl extends BaseDao implements IMaterialDao {

    @Override
    public List<Object> getMaterialListByOrclNotInCode(String materialTypeId, String materialName, Integer start, Integer limit) throws Exception {

        String sql = "select * from (";
        sql += "select a.* ,rownum rn from ( select code,name,materialspec,materialtype,unit,pk_material,pk_marbasclass from material_view where 1 = 1";
        if (!StringUtils.isEmpty(materialTypeId)) {
            sql += " and pk_marbasclass = '" + materialTypeId + "'";
        }
        if (!StringUtils.isEmpty(materialName)) {
            sql += " and name like '%" + materialName + "%'";
        }
        sql += ") a where rownum <= " + limit + " ) b where rn > " + start + "";

        List<Object> list = this.getDataBySql(sql);
        return list;
    }

    @Override
    public List<Object> getMaterialListByObj(String materialTypeId, String materialName, Integer start, Integer limit) throws Exception {
        String sql = "select * from (";
        sql += "select a.* ,rownum rn from ( select code,name,materialspec,materialtype,unit,pk_material,pk_marbasclass from material_view where 1 = 1";
        if (!StringUtils.isEmpty(materialTypeId)) {
            sql += " and pk_marbasclass = '" + materialTypeId + "'";
        }
        if (!StringUtils.isEmpty(materialName)) {
            sql += " and name like '%" + materialName + "%'";
        }
        sql += ") a where rownum <= " + limit + " ) b where rn > " + start + "";

        List<Object> list = this.getDataBySql(sql);
        return list;
    }

    @Override
    public List<Object> queryMaterialAndImgsListOneByCode(String code) throws Exception {
        String sql = "select b.imgUrl from sync_material a,material_imgs b where a.CODE = b.code and a.CODE = '" + code + "'";
        List<Object> list = this.getDataBySqlForMysql(sql);
        return list;
    }

    @Override
    public List<Object> getMaterialByOrgIdAndCode(String code) throws Exception {
        //materialspec 规格
        //materialtype 型号
        //unit 计量单位
        String sql = "SELECT CODE,NAME,PK_MATERIAL,materialspec,materialtype,unit,pk_marbasclass FROM material_view WHERE code = '" + code + "'";
        List<Object> list = this.getDataBySql(sql);
        return list;
    }

    @Override
    public List<Object> getMaterialList(Integer start, Integer limit, String materialName) throws Exception {
        String sql = "select * from ( select a.* ,rownum rn from ( select code,name,materialspec,materialtype,unit,pk_material,pk_marbasclass from material_view where 1 = 1";
        if (StringUtils.isEmpty(materialName)) {
            sql += " and name like '%" + materialName + "%'";
        }
        sql += ") a where rownum <= " + limit + " ) b where rn > " + start + "";
        List<Object> list = this.getDataBySql(sql);
        return list;
    }

    @Override
    public List<Object> getMaterialListCodeNotIn(Integer start, Integer limit, String materialName, String codeList) throws Exception {
        String sql = "select * from ( select a.* ,rownum rn from ( select code,name,materialspec,materialtype,unit,pk_material,pk_marbasclass from material_view where code not in (" + codeList + ")";
        if (StringUtils.isEmpty(materialName)) {
            sql += " and name like '%" + materialName + "%'";
        }
        sql += ") a where rownum <= " + limit + " ) b where rn > " + start + "";
        List<Object> list = this.getDataBySql(sql);
        return list;
    }

    @Override
    public List<Object> queryAllMaterialListByOrcl() throws Exception {
        String sql = "select code,name,materialspec,materialtype,unit,pk_material,pk_marbasclass from material_view";
        List<Object> list = this.getDataBySql(sql);
        return list;
    }

    @Override
    public List<Object> querySyncMaterialForMysql() throws Exception {
        String sql = "select * from sync_material";
        List<Object> list = this.getDataBySqlForMysql(sql);
        return list;
    }

    @Override
    public void truncateSyncMaterial() throws Exception {
        String sql = "truncate sync_material";
        this.updateForMysql(sql);
    }

    @Override
    public int[] batchInsertMaterialForMysql(List<Object[]> queryList) {
        String sql = "insert into sync_material (UNIT,NAME,PK_MATERIAL,MATERIALTYPE,PK_MARBASCLASS,MATERIALSPEC,CODE,CREATEDATE) values(?,?,?,?,?,?,?,?)";
        int[] ints = this.batchUpdateForMysql(sql, queryList);
        return ints;
    }

    @Override
    public List<Object> queryTodoMaterial(String materialName, Integer start, Integer limit) throws Exception {
        String sql = "SELECT * from sync_material where 1 = 1";
        if (!StringUtils.isEmpty(materialName)) {
            sql += " and name like '" + materialName + "'";
        }
        sql += " and CODE not in (SELECT code from material_imgs) limit " + start + "," + limit + "";
        List<Object> list = this.getDataBySqlForMysql(sql);
        return list;
    }

    @Override
    public List<Object> queryDoneMaterial(String materialName, Integer start, Integer limit) throws Exception {
        String sql = "select * from material_imgs a LEFT JOIN sync_material b ON (a.code = b.CODE) where 1 = 1";
        if (!StringUtils.isEmpty(materialName)) {
            sql += " and b.name like '%" + materialName + "%'";
        }
        sql += " limit " + start + "," + limit + "";
        List<Object> list = this.getDataBySqlForMysql(sql);
        return list;
    }

    @Override
    public List<Object> queryDoneMaterialListByDistinctCode(Integer start, Integer limit) throws Exception {
        String sql = "select * from sync_material where CODE in (SELECT DISTINCT(code) from material_imgs) limit " + start + "," + limit + "";
        return this.getDataBySqlForMysql(sql);
    }

    @Override
    public List<Object> queryMaterialImgsListByCode(String code) throws Exception {
        String sql = "select imgUrl from material_imgs where code = '" + code + "'";
        return this.getDataBySqlForMysql(sql);
    }

    @Override
    public void insertImgs(String code, String imgUrl) throws Exception {
        String insertImg = "insert into material_imgs (code,imgUrl) values('" + code + "','" + imgUrl + "')";
        this.updateForMysql(insertImg);
    }

    @Override
    public Integer queryCountMaterial(String state, String materialName) throws Exception {
        String sql = "";
        if (state.equals("todo")) {
            sql = "SELECT count(*) from sync_material where 1 = 1";
            if (!StringUtils.isEmpty(materialName)) {
                sql += " and name like '" + materialName + "'";
            }
            sql += " and CODE not in (SELECT code from material_imgs) ";
        }
        if (state.equals("done")) {
            sql = "select count(*) from sync_material where CODE in (SELECT DISTINCT(code) from material_imgs) ";
        }
        Integer count = this.queryCountForMysql(sql);
        return count;
    }

    @Override
    public Integer queryCountMaterialListByObj(String materialTypeId, String materialName) throws Exception {
        String sql = "select count(*) from material_view where 1 = 1";
        if (!StringUtils.isEmpty(materialTypeId)) {
            sql += " and pk_marbasclass = '" + materialTypeId + "'";
        }
        if (!StringUtils.isEmpty(materialName)) {
            sql += " and name like '%" + materialName + "%'";
        }
        Integer count = this.queryCountForMysql(sql);
        return count;
    }


}
